<?php
include "otamu_config.php";
define("_VIEWPATH_","views/irou/");
$layout = "layout";

$OBJECT = 'master_institusi';
$OBJECT_TABLE = $OBJECT;

$TABLE_GURU = "guru_staf";

if (!isset($_GET['act'])) $_GET['act'] = ''; 

class Ahli extends ADOdb_Active_Record
{
	var $_table = "kunci_induk";
}

class Pribadi extends ADOdb_Active_Record
{
	var $_table = "pribadi";
}

class Perkhidmatan extends ADOdb_Active_Record
{
	var $_table = "perkhidmatan";
}

class Penama extends ADOdb_Active_Record
{
	var $_table = "penama";
}

class Payment extends ADOdb_Active_Record
{
	var $_table = "payment";
}

$irou_months = array('Apr','Mei','Jun','Jul','Ogos','Sept','Okt','Nov','Dis','Jan','Feb','Mac');
$irou_month_nums = array('4','5','6','7','8','9','10','11','12','1','2','3');

switch ($_GET['act']) {

	case "file_upload";
		//$ahli = new Ahli();
		//$ahli->no_ahli = get_latest_no_ahli() + 1;
	break;
		
	case "process_file";
		$uploaddir = "uploads/";
		
		$SafeFile = $HTTP_POST_FILES['the_file']['name'];
		$path = $uploaddir.$SafeFile;

		if($ufile != none){ //AS LONG AS A FILE WAS SELECTED...

			if(copy($HTTP_POST_FILES['the_file']['tmp_name'], $path)){ //IF IT HAS BEEN COPIED...
	
				//GET FILE NAME
				$theFileName = $HTTP_POST_FILES['the_file']['name'];
		
				//GET FILE SIZE
				$theFileSize = $HTTP_POST_FILES['the_file']['size'];
			}
		}
		$lines = file($path);
		
		$no_ahlis = array();
		$no_kps = array();
		$yurans = array();
		$sumbangans = array();


$headers = array('jenis_rekod','kod_kp','no_kp','kod_koop','no_ahli','bulan_potongan','kod_acc_potongan',
                 'jum_tuntutan','jum_bayaran','kod_majikan','no_pekerja','kod_sebab','no_baucer','no_larian','no_kp_lama');
$details = array('jenis_rekod','kod_kp','no_kp','kod_koop','no_ahli','bulan_potongan','kod_acc_potongan',
                 'jum_pokok','jum_faedah','nama_ahli','no_kp_lama');

		foreach ($lines as $line ) {
			$jenis_rekod      = substr($line,0,1);//1
			$kod_kp           = substr($line,1,1);//1
			$no_kp            = substr($line,2,12);//12
			$kod_coop         = substr($line,14,5);//5
			$no_ahli          = substr($line,19,12);//12
				$no_ahli = ereg_replace("A","",$no_ahli); // kena buang "A"
			$sesi_tahun       = substr($line,31,4);//4
			$bulan_potongan   = substr($line,35,2);//2
			$kod_acc_potongan = substr($line,37,4);//4
						
			if ($jenis_rekod == '0') {
				$no_ahlis[] = $no_ahli; 
				$no_kps[]   = $no_kp; 
				//header
				$jum_tuntutan     = substr($line,41,7);//7 (9999.99)
				$jum_bayaran      = substr($line,48,7);//7
				$kod_majikan      = substr($line,55,12);//12
				$no_pekerja       = substr($line,67,10);//10
				$kod_sebab        = substr($line,77,1);//1
				$no_baucer        = substr($line,78,7);//7
				$no_larian        = substr($line,85,5);//5
				$no_kp_lama       = substr($line,90,10);//10
				//$str .= "header: <br>\n";

				//debug
/*				echo "<table border=1>\n";
				echo " <tr>\n";
				foreach ($headers as $h) {
					echo "<td bgcolor=\"grey\">$h</td>";
				}
				echo " </tr>\n";
				echo " <tr>\n";
				foreach ($headers as $h) {
					echo "<td>".$$h."</td>";
				}
				echo " </tr></table>\n";
*/
			}else {
				$jum_pokok        = substr($line,41,7);//7 (9999.99)
				$jum_pokok        = sprintf("%05d",substr($jum_pokok,0,5))+0;
				$jum_faedah       = substr($line,48,7);//7
				$nama_ahli        = substr($line,55,35);//40
				$no_kp_lama       = substr($line,90,10);//10
				
				if ($kod_acc_potongan == '1001') { $yurans["$no_ahli"]      = sprintf("%05d",substr($jum_pokok,0,5))+0; }
				if ($kod_acc_potongan == '1002') { $sumbangans["$no_ahli"]  = sprintf("%05d",substr($jum_pokok,0,5))+0; }
			
				//debug
/*				echo "<table border=1>\n";
				echo " <tr>\n";
				foreach ($details as $h) {
					echo "<td bgcolor=\"grey\">$h</td>";
				}
				echo " </tr>\n";
				echo " <tr>\n";
				foreach ($details as $h) {
					echo "<td>".$$h."</td>";
				}
				echo " </tr></table>\n";
*/				//eodebug

				$payment_types = array ("1001"=>"Yuran","1002"=>"Sumbangan", "0501"=>"Insuran");
				//echo $no_ahli;
				//echo ": ";
				$no_ahli = ereg_replace(" ","",$no_ahli);
				$ahli = new Ahli();
				if ($ahli->Load("no_ahli='$no_ahli'")) {
					//echo "ahli_id=$ahli->id , kod_acc_potongan=$kod_acc_potongan, tahun=$sesi_tahun, bulan=$bulan_potongan, jum=$jum_pokok<br>\n";
					if (doPayment($ahli->id,$kod_acc_potongan,$sesi_tahun,$bulan_potongan,$jum_pokok)) {
						$str .= "<span style=\"color:green;\">No Ahli $no_ahli : Payment ($payment_types[$kod_acc_potongan]) success: $ahli->nama_ahli RM$jum_pokok</span>";
					}
					else {
						$str .= "<span style=\"color:red;\">No Ahli $no_ahli : Payment failed: $ahli->nama_ahli</span>";
					}
				}
				else {
					$str .= "<span style=\"color:red;\">No Ahli $no_ahli : No Ahli tidak wujud.</span>";
				}
				$str .= "<br>";
			}			
		}
		//exit;
			//print_r($yurans);exit;
	break;
		
	case "search_ahli":
		$search = false;
		$limit = 50;
		if (isset($_REQUEST['q'])) :
			$search = true;		
			$params = array();
			$where_arr = array();
			$paginate_arr = array();

			// Do sorting 
			$sorting_idx = array(1=>"ki.no_ahli", 2=>"nama_ahli", 3=>"cawangan_id",4=>"keahlian",5=>"tm.status_murid_id");
			$sort_asc = (isset($_REQUEST['sort_asc']) && $_REQUEST['sort_asc'] != "" ) ? ($_REQUEST['sort_asc'] == "ASC")? "DESC": "ASC" : "ASC";
			$sortby = (isset($_REQUEST['idx']) && $_REQUEST['idx'] !='') ? $_REQUEST['idx'] : 1;

			$_SESSION['sortby'] = $sortby;
			$_SESSION['sort_asc'] = $sort_asc;
			
			$show_img = array();
			$img = ($_SESSION['sort_asc'] == "ASC") ? "<img src='images/asc.png' alt=''/>" : "<img src='images/desc.png' alt=''/>";
			$srt_flip = array_flip($sorting_idx);
			if(in_array($_SESSION['sortby'], $srt_flip)){
				$show_img[$_SESSION['sortby']] = $img;
			}
			
			// Process input
			if ($_REQUEST['n'] != '') { 
				$where_arr[]  = "ki.no_ahli = '$_REQUEST[n]'" ;
				$paginate_arr[]	= "n=$_REQUEST[n]";
				$params[] = "no_ahli = '$_REQUEST[n]'";
			} else {
				$paginate_arr[]	= "n=";
			}

			if ($_REQUEST['q'] != '') { 
				$keyword       = $_REQUEST['q'];
				$where_arr[] = "nama_ahli ILIKE '%$keyword%'" ;
				$paginate_arr[]	= "q=$_REQUEST[q]";
				$params[] = "nama_ahli ILIKE '%$keyword%'";
			} else {
				$paginate_arr[]	= "q=";
			}

			if ($_REQUEST['b'] != '') { 
				$where_arr[] = "nokp_baru ILIKE '%$_REQUEST[b]%'" ;
				$paginate_arr[]	= "b=$_REQUEST[b]";
			}

			if ($_REQUEST['l'] != '') { 
				$where_arr[] = "nokp_lama ILIKE '%$_REQUEST[l]%'" ;
				$paginate_arr[]	= "l=$_REQUEST[l]";
			}
			
			if ($_REQUEST['tm'] != '') { 
				$where_arr[] = "tarikh_masuk = '$_REQUEST[tm]'" ;
				$paginate_arr[]	= "tm=$_REQUEST[tm]";
			}
			
			if ($_REQUEST['tb'] != '') { 
				$where_arr[] = "tarikh_berhenti = '$_REQUEST[tb]'" ;
				$paginate_arr[]	= "tb=$_REQUEST[tb]";
			}
			
			if ($_REQUEST['stop'] != '') { 
				$where_arr[] = "sebab_berhenti ILIKE '$_REQUEST[stop]'" ;
				$paginate_arr[]	= "sb=$_REQUEST[sb]";
			}
			
			// Do filtering
			if ($_REQUEST['c'] != '') { 
				$where_arr[] = "kh.cawangan_id = '$_REQUEST[c]'" ;
				$paginate_arr[]	= "c=$_REQUEST[c]";
			}
			
			if ($_REQUEST['k'] != '') { 
				if ($_REQUEST['k'] == 'Tiada Status') :
					$where_arr[] = "pr.keahlian IS NULL" ;
					$paginate_arr[]	= "k=Tiada Status";				
				else:
					$where_arr[] = "pr.keahlian = '$_REQUEST[k]'" ;
					$paginate_arr[]	= "k=$_REQUEST[k]";
				endif;
			}
			//end filtering
			
			if ($_REQUEST['idx'] != '') { 
				$paginate_arr[]	= "idx=$_REQUEST[idx]";
			}
			if ($_REQUEST['sort_asc'] != '') { 
				$paginate_arr[]	= "sort_asc=$_REQUEST[sort_asc]";
			}

			//paging
			if (empty($_REQUEST['p'])) {
				$page = 1;
				$range = " LIMIT $limit";
			}
			else {
				$page = $_REQUEST['p'];
				$offset = "OFFSET " . ($page-1) * $limit;
				$range = "  LIMIT $limit";			
			}

			//finish where_clause
			$where_clause = join(" AND ", $where_arr);
			if ($where_clause != '') $where_clause = "WHERE " . $where_clause;

			$sql_total_cnt = "SELECT count(ki.*) FROM kunci_induk ki 
			        LEFT JOIN perkhidmatan kh ON kh.no_ahli=ki.no_ahli
					LEFT  JOIN pribadi pr ON pr.no_ahli=ki.no_ahli
					$where_clause";
					//echo $sql_total_cnt;exit;
			if ($res = $DB->Execute($sql_total_cnt)) {
				$dat = $res->FetchNextObject();
				$all_count = $dat->COUNT;
				$last_page = floor($all_count/$limit) + 1;
			}else {
				$_SESSION['error'] = "Error SQL";
				$all_count = 0;
			}
					
			$sql = "SELECT ki.*,kh.cawangan_id,pr.keahlian FROM kunci_induk ki 
			        LEFT JOIN perkhidmatan kh ON kh.no_ahli=ki.no_ahli
					LEFT  JOIN pribadi pr ON pr.no_ahli=ki.no_ahli
					$where_clause
					ORDER BY ".$sorting_idx[$_SESSION['sortby']]." ".$_SESSION['sort_asc']."
					$range
					$offset
					";
			//echo $sql;
			//exit;		
			if ($res = $DB->Execute($sql)) {
				$res_count = $res->RecordCount();
				$paginate_str = join("&",$paginate_arr);
			}else {
				$_SESSION['error'] = "Error SQL";
			}	
		endif;
		
	break;

	case "new_ahli";
		$ahli = new Ahli();
		$ahli->no_ahli = get_latest_no_ahli() + 1;
	break;
		
	case "edit_ahli":
		if ($_SESSION['ACCESS_LEVEL']!='1') {
			exitError("Access Denied");
		}
	case "view_ahli" :
		$ahli = new Ahli();
		$ahli->Load("id='$_REQUEST[id]'");

		$pribadi = new Pribadi();
		$pribadi->Load("no_ahli='$ahli->no_ahli'");

		$khidmat = new Perkhidmatan();
		$khidmat->Load("no_ahli='$ahli->no_ahli'");
		//print_r($khidmat);exit;

		$penama = new Penama();
		$penama->Load("no_ahli='$ahli->no_ahli'");
		
		break;

	case "create_ahli" :
		//print_r($_REQUEST);exit;
		
		//proses tarikh
		$tarikhs = array("ahli:tarikh_masuk","ahli:tarikh_berhenti","pribadi:tarikh_lahir");
		foreach ($tarikhs as $xy ) {
			list($a,$b) = split(":",$xy);
			if ($_REQUEST[$a][$b] != '' ) {
				$_REQUEST[$a][$b] = ymd_date($_REQUEST[$a][$b]);
			}
		}
		
		//remember first
		$ahli = new Ahli();		
		foreach ($_REQUEST[ahli] as $k => $v) {
			$ahli->$k = $v;
			if (empty($v)) $ahli->$k = NULL;
		}
		$pribadi = new Pribadi();
		foreach ($_REQUEST[pribadi] as $k => $v) {
			$pribadi->$k = $v;
			if (empty($v)) $pribadi->$k = NULL;
		}
		$khidmat = new Perkhidmatan();
		foreach ($_REQUEST[khidmat] as $k => $v) {
			$khidmat->$k = $v;
			if (empty($v)) $khidmat->$k = NULL;
		}
		$penama = new Penama();
		foreach ($_REQUEST[penama] as $k => $v) {
			$penama->$k = $v;
			if (empty($v)) $penama->$k = NULL;
		}
		//end remember
						
		//check duplicate nokp_baru ,lama
		// 12/05/2011 ## takyah check la ... org dah berenti buleh jadi ahli baru dgn nombo ahli baru 
		// ini syarat dia: kalau dah pernah jadi ahli, tapi status_keahlian=berhenti, then boleh create as new ahli
		$nokp_baru = $_REQUEST['ahli']['nokp_baru'];
		//$sql = "SELECT * FROM kunci_induk WHERE nokp_baru = '$nokp_baru'";
		$sql = "SELECT * FROM kunci_induk ki
		        INNER JOIN pribadi p on p.no_ahli=ki.no_ahli
				WHERE nokp_baru = '$nokp_baru'
				AND p.keahlian = 'Aktif'
		        ";
		$res = $DB->Execute($sql);
		if ($res->RecordCount() > 0) {
			$_SESSION['error'] = "Error: No KP (Baru) telah wujud dah ahli masih aktif. Sila ubah status keahlian yang lama kepada 'Berhenti'";
			$act = "new_ahli";
			break;
		}

		
		// if empty nokp_lama xyah la buat mende nih
		$nokp_lama = $_REQUEST['ahli']['nokp_lama'];
		// 12/05/2011 ## takyah check la ... org dah berenti buleh jadi ahli baru dgn nombo ahli baru
		
		if(!empty($nokp_lama) || $nokp_lama != ''):
		//$sql = "SELECT * FROM kunci_induk WHERE nokp_lama = '$nokp_lama'";
		$sql = "SELECT * FROM kunci_induk ki
		        INNER JOIN pribadi p on p.no_ahli=ki.no_ahli
				WHERE nokp_lama = '$nokp_lama'
				AND p.keahlian = 'Aktif'
		        ";
		$res = $DB->Execute($sql);
		if ($res->RecordCount() > 0) {
			$_SESSION['error'] = "Error: No KP (Lama) telah wujud dah ahli masih aktif. Sila ubah status keahlian yang lama kepada 'Berhenti'";
			$act = "new_ahli";
			break;
		}
		endif;
		
		
		if (!$ahli->save()) { 
			$_SESSION['error'] = "Ahli : " . $ahli->ErrorMsg();
			$act = "new_ahli";
			break;
		}

		//pribadi
		$pribadi->no_ahli = $ahli->no_ahli;		
		if (!$pribadi->save()) { 
			$_SESSION['error'] = "Profil : " . $pribadi->ErrorMsg();
			$act = "new_ahli";
			break;
		}

		//khidmat
		$khidmat->no_ahli = $ahli->no_ahli;
		if (!$khidmat->save()) { 
			$_SESSION['error'] = "Perkhidmatan : " . $khidmat->ErrorMsg();
			$act = "new_ahli";
			break;
		}

		$penama->no_ahli = $ahli->no_ahli;
		if (!$penama->save()) { 
			$_SESSION['error'] = "Penama : " . $penama->ErrorMsg();
			$act = "new_ahli";
			break;
		}
		
		$_SESSION['flash'] = 'Ahli baru berjaya ditambah.';
		header("Location: ?act=view_ahli&id=$ahli->id");
		break;
	
	case "update_ahli" :
		//proses tarikh
		$tarikhs = array("ahli:tarikh_masuk","ahli:tarikh_berhenti","pribadi:tarikh_lahir");
		foreach ($tarikhs as $xy ) {
			list($a,$b) = split(":",$xy);
			if ($_REQUEST[$a][$b] != '' ) {
				$_REQUEST[$a][$b] = ymd_date($_REQUEST[$a][$b]);
			}
		}
		
		$ahli = new Ahli();
		$ahli->Load("id='$_REQUEST[id]'");
		
		//!must check no_ahli, cannot duplicate
		foreach ($_REQUEST[ahli] as $k => $v) {
			$ahli->$k = $v;
			if (empty($v)) $ahli->$k = NULL;
			if ($v=='0') $ahli->$k = 0;
		}
		
		if (!$ahli->save()) { 
			$_SESSION['error'] = "Ahli : " . $ahli->ErrorMsg();
			$act = "edit_ahli";
			break;
		}

		//pribadi
		$pribadi = new Pribadi();
		$pribadi->Load("no_ahli=$ahli->no_ahli");

		// Gmi added 12.05.2011 [Kalu xde row camne ??] camni la kot
		$pribadi->no_ahli= $ahli->no_ahli;
		foreach ($_REQUEST[pribadi] as $k => $v) {
			$pribadi->$k = $v;
			if (empty($v)) $pribadi->$k = NULL;
		}
		//print_r($pribadi); exit;
		if (!$pribadi->save()) { 
			$_SESSION['error'] = "Profil : " . $pribadi->ErrorMsg();
			$act = "edit_ahli";
			break;
		}

		//khidmat
		$khidmat = new Perkhidmatan();
		$khidmat->Load("no_ahli='$ahli->no_ahli'");
		// Gmi added 12.05.2011 [Kalu xde row camne ??] camni la kot
		$khidmat->no_ahli = $ahli->no_ahli;
		foreach ($_REQUEST[khidmat] as $k => $v) {
			$khidmat->$k = $v;
			if (empty($v)) $khidmat->$k = NULL;
	}
		if (!$khidmat->save()) { 
			$_SESSION['error'] = "Perkhidmatan : " . $khidmat->ErrorMsg();
			$act = "edit_ahli";
			break;
		}

		$penama = new Penama();
		$penama->Load("no_ahli='$ahli->no_ahli'");
		// Gmi added 12.05.2011 [Kalu xde row camne ??] camni la kot
		$penama->no_ahli = $ahli->no_ahli;

		foreach ($_REQUEST[penama] as $k => $v) {
			$penama->$k = $v;
			if (empty($v)) $penama->$k = NULL;
		}
		$penama->no_ahli = $ahli->no_ahli;
		//print_r($penama);
		if (!$penama->save()) { 
			$_SESSION['error'] = "Penama : " . $penama->ErrorMsg();
			$act = "edit_ahli";
			break;
		}
		
		$_SESSION['flash'] = 'Maklumat ahli telah berjaya dikemaskini.';
		header("Location: ?act=view_ahli&id=$ahli->id");
		break;

	case "edit_penyata" :
		if ($_SESSION['ACCESS_LEVEL']!='1') {
			exitError("Access Denied");
		}
	case "view_penyata" :
		$current_year = Date("Y");
		$selected_year = isset($_REQUEST['sesi_tahun'])? $_REQUEST['sesi_tahun'] : $current_year;
		$current_month = Date(m)+0;
		//echo $current_month;exit;
		$ahli = new Ahli();
		$ahli->Load("id='$_REQUEST[id]'");

		$pribadi = new Pribadi();
		$pribadi->Load("no_ahli='$ahli->no_ahli'");

		$khidmat = new Perkhidmatan();
		$khidmat->Load("no_ahli='$ahli->no_ahli'");

		$yurans = $sumbangans = $tabungs = $insurans = Array();
		$sql = "SELECT * FROM payment WHERE ahli_id='$ahli->id' AND sesi_tahun='$selected_year'";
		if ($res = $DB->Execute($sql)) {
			while ($dat = $res->FetchNextObject()) :
				$dat = decapitalize($dat);
				$month = $dat->month;
				$yurans[$month]     = $dat->yuran_amount;
				$tabungs[$month]    = $dat->tabung_amount;
				$sumbangans[$month] = $dat->sumbangan_amount;
				$insurans[$month]   = $dat->insuran_amount;
				$a_yuran_masuk[$month]   = $dat->a_yuran_masuk;
				$b_baki_bulan_lepas[$month]   = $dat->b_baki_bulan_lepas;
				$c_yuran[$month]              = $dat->c_yuran;
				$d_mesti_bayar[$month]        = $dat->d_mesti_bayar;
				if ($d_mesti_bayar[$month] == '') {
					$d_mesti_bayar[$month] = $WAJIB_YURAN + $WAJIB_SUMBANGAN + $WAJIB_TABUNG;;
				}
				$e_jumlah_sudah_bayar[$month] = $dat->e_jumlah_sudah_bayar;
				$f_baki_mesti_bayar[$month]   = $dat->f_baki_mesti_bayar;

			endwhile;
		}
		//print_r($insurans);exit;
		break;

	case "update_penyata" :
		//print_r($_REQUEST);exit;
		$ahli = new Ahli();
		$ahli->Load("id='$_REQUEST[id]'");
		foreach ($irou_month_nums as $month) {
			$y = $_REQUEST['yuran']["$month"];
			$s = $_REQUEST['sumbangan']["$month"];
			$t = $_REQUEST['tabung']["$month"];
			$i = $_REQUEST['insuran']["$month"];
			$payment = new Payment();
			if ($payment->Load("ahli_id=$_REQUEST[id] AND month=$month AND sesi_tahun='$_REQUEST[sesi_tahun]'")) { //exist already
				if (($y!=$payment->yuran_amount) || ($s!=$payment->sumbangan_amount) || ($t!=$payment->tabung_amount) 
				    || ($i!=$payment->insuran_amount)
				    || ($payment->b_baki_bulan_lepas   != $_REQUEST['b_baki_bulan_lepas']["$month"])
				    || ($payment->c_yuran              != $_REQUEST['c_yuran']["$month"])
				    || ($payment->d_mesti_bayar        != $_REQUEST['d_mesti_bayar']["$month"])
				    || ($payment->e_jumlah_sudah_bayar != $_REQUEST['e_jumlah_sudah_bayar']["$month"])
				    || ($payment->f_baki_mesti_bayar   != $_REQUEST['f_baki_mesti_bayar']["$month"])
					)
				{ //if any changes
					$payment->yuran_amount = ($y=='')?NULL:$y;
					$payment->sumbangan_amount = ($s=='')?NULL:$s;
					$payment->tabung_amount = ($t=='')?NULL:$t;
					$payment->insuran_amount = ($i=='')?NULL:$i;


					//added 02/07/11 SHAM
					$payment->a_yuran_masuk = '77';
					$payment->b_baki_bulan_lepas = $_REQUEST['b_baki_bulan_lepas']["$month"];
					$payment->c_yuran = $WAJIB_YURAN + $WAJIB_SUMBANGAN + $WAJIB_TABUNG;
					$payment->d_mesti_bayar = $WAJIB_YURAN + $WAJIB_SUMBANGAN + $WAJIB_TABUNG;
					$payment->e_jumlah_sudah_bayar = $y + $s + $t;
					$payment->f_baki_mesti_bayar = ($payment->b_baki_bulan_lepas + $payment->d_mesti_bayar) - $payment->e_jumlah_sudah_bayar;
					//sleep(10);
					//end 02/07/11 SHAM
					
					//print_r($payment);exit;
					
					$payment->save();
				}
				//echo "already exist";exit;
			}
			else { //not exist yet
				$payment->ahli_id = $ahli->id;
				$payment->month   = $month;
				$payment->sesi_tahun = $_REQUEST['sesi_tahun'];
				$payment->yuran_amount = ($y=='')?NULL:$y;
				$payment->sumbangan_amount = ($s=='')?NULL:$s;
				$payment->insuran_amount = ($i=='')?NULL:$i;
				$payment->date_paid = Date("Y-m-d");
				$payment->save();
				//echo "not exist";exit;
			}

		$_SESSION['flash'] = 'Penyata telah berjaya dikemaskini.';
		header("Location: ?act=view_penyata&id=$ahli->id");
			
			
			//if any changes then update, if none->insert new entry
			
			//print_r($payment);exit;
		}		
	break;

	case "payment_individu_search":
		$current_year = Date("Y");
		$selected_year = isset($_REQUEST['sesi_tahun'])? $_REQUEST['sesi_tahun'] : $current_year;

		$search = false;
		$limit = 20;
		if (isset($_REQUEST['q'])) :
			$search = true;		
			$params = array();
			$where_arr = array();
			$paginate_arr = array();

			// Do sorting 
			$sorting_idx = array(1=>"ki.no_ahli", 2=>"nama_ahli", 3=>"cawangan",4=>"keahlian",5=>"tm.status_murid_id");
			$sort_asc = (isset($_REQUEST['sort_asc']) && $_REQUEST['sort_asc'] != "" ) ? ($_REQUEST['sort_asc'] == "ASC")? "DESC": "ASC" : "ASC";
			$sortby = (isset($_REQUEST['idx']) && $_REQUEST['idx'] !='') ? $_REQUEST['idx'] : 1;

			$_SESSION['sortby'] = $sortby;
			$_SESSION['sort_asc'] = $sort_asc;
			
			$show_img = array();
			$img = ($_SESSION['sort_asc'] == "ASC") ? "<img src='images/asc.png' alt=''/>" : "<img src='images/desc.png' alt=''/>";
			$srt_flip = array_flip($sorting_idx);
			if(in_array($_SESSION['sortby'], $srt_flip)){
				$show_img[$_SESSION['sortby']] = $img;
			}
			
			// Process input
			if ($_REQUEST['n'] != '') { 
				$where_arr[]  = "ki.no_ahli = '$_REQUEST[n]'" ;
				$paginate_arr[]	= "n=$_REQUEST[n]";
				$params[] = "no_ahli = '$_REQUEST[n]'";
			} else {
				$paginate_arr[]	= "n=";
			}

			if ($_REQUEST['q'] != '') { 
				$keyword       = $_REQUEST['q'];
				$where_arr[] = "nama_ahli ILIKE '%$keyword%'" ;
				$paginate_arr[]	= "q=$_REQUEST[q]";
				$params[] = "nama_ahli ILIKE '%$keyword%'";
			} else {
				$paginate_arr[]	= "q=";
			}

			if ($_REQUEST['b'] != '') { 
				$where_arr[] = "nokp_baru ILIKE '%$_REQUEST[b]%'" ;
				$paginate_arr[]	= "b=$_REQUEST[b]";
			}

			if ($_REQUEST['l'] != '') { 
				$where_arr[] = "nokp_lama ILIKE '%$_REQUEST[l]%'" ;
				$paginate_arr[]	= "l=$_REQUEST[l]";
			}
			
		
			// Do filtering
			if ($_REQUEST['c'] != '') { 
				$where_arr[] = "kh.cawangan_id = '$_REQUEST[c]'" ;
				$paginate_arr[]	= "c=$_REQUEST[c]";
			}
			
			if ($_REQUEST['k'] != '') { 
				$where_arr[] = "kh.cawangan_id = '$_REQUEST[c]'" ;
				$paginate_arr[]	= "sb=$_REQUEST[sb]";
			}
			//end filtering
			
			if ($_REQUEST['idx'] != '') { 
				$paginate_arr[]	= "idx=$_REQUEST[idx]";
			}
			if ($_REQUEST['sort_asc'] != '') { 
				$paginate_arr[]	= "sort_asc=$_REQUEST[sort_asc]";
			}

			//finish where_clause
			$where_clause = join(" AND ", $where_arr);
			if ($where_clause != '') $where_clause = "WHERE " . $where_clause;

			$sql_total_cnt = "SELECT count(ki.*) FROM kunci_induk ki 
			        INNER JOIN perkhidmatan kh ON kh.no_ahli=ki.no_ahli
					LEFT  JOIN pribadi pr ON pr.no_ahli=ki.no_ahli
					$where_clause";
					//echo $sql_total_cnt;exit;
			if ($res = $DB->Execute($sql_total_cnt)) {
				$dat = $res->FetchNextObject();
				$res_count = $dat->COUNT;
				$last_page = floor($total_count/$limit) + 1;
			}else {
				$_SESSION['error'] = "Error SQL";
				$res_count = 0;
			}
					
			$sql = "SELECT ki.*,kh.cawangan,pr.keahlian FROM kunci_induk ki 
			        INNER JOIN perkhidmatan kh ON kh.no_ahli=ki.no_ahli
					LEFT  JOIN pribadi pr ON pr.no_ahli=ki.no_ahli
					$where_clause
					ORDER BY ".$sorting_idx[$_SESSION['sortby']]." ".$_SESSION['sort_asc']."
					$range
					$offset
					";
			if ($res = $DB->Execute($sql)) {
				$res_count = $res->RecordCount();
				$paginate_str = join("&",$paginate_arr);
			}else {
				$_SESSION['error'] = "Error SQL";
			}	
		endif;
		
	break;

	case "payment_bulk_search":
		//set default value
		if (!isset($_REQUEST['k'])) $_REQUEST['k'] = "Aktif"; 

		$current_year = Date("Y");
		$selected_year = isset($_REQUEST['sesi_tahun'])? $_REQUEST['sesi_tahun'] : $current_year;
		$selected_month = isset($_REQUEST['month'])? $_REQUEST['month'] : (date(m)+0);
		
		$limit = 1000;
		if (isset($_REQUEST['n1']) || isset($_REQUEST['n2']) || isset($_REQUEST['c']) || isset($_REQUEST['Cari'])) :
			$search = true;		
			$params = array();
			$where_arr = array();
			$paginate_arr = array();

			// Do sorting 
			$sorting_idx = array(1=>"ki.no_ahli", 2=>"nama_ahli", 3=>"cawangan",4=>"keahlian",5=>"tm.status_murid_id");
			$sort_asc = (isset($_REQUEST['sort_asc']) && $_REQUEST['sort_asc'] != "" ) ? ($_REQUEST['sort_asc'] == "ASC")? "DESC": "ASC" : "ASC";
			$sortby = (isset($_REQUEST['idx']) && $_REQUEST['idx'] !='') ? $_REQUEST['idx'] : 1;

			$_SESSION['sortby'] = $sortby;
			$_SESSION['sort_asc'] = $sort_asc;
			
			$show_img = array();
			$img = ($_SESSION['sort_asc'] == "ASC") ? "<img src='images/asc.png' alt=''/>" : "<img src='images/desc.png' alt=''/>";
			$srt_flip = array_flip($sorting_idx);
			if(in_array($_SESSION['sortby'], $srt_flip)){
				$show_img[$_SESSION['sortby']] = $img;
			}
			
			// Process input
			//if ($_REQUEST['n1'] != '') $where_arr[]  = "ki.no_ahli = '$_REQUEST[n1]'" ;
			if ( ($_REQUEST['n1'] != '') && ($_REQUEST['n2'] != '')) {
				$where_arr[]  = "(ki.no_ahli BETWEEN $_REQUEST[n1] AND $_REQUEST[n2])";
				$paginate_arr[]	= "n1=$_REQUEST[n1]&n2=$_REQUEST[n2]";
			}else{
				if ($_REQUEST['n1'] != '') {
					$where_arr[]  = "(ki.no_ahli = $_REQUEST[n1])";
					$paginate_arr[]	= "n1=$_REQUEST[n1]";
				}
				if ($_REQUEST['n2'] != '') {
					$where_arr[]  = "(ki.no_ahli = $_REQUEST[n2])";
					$paginate_arr[]	= "n2=$_REQUEST[n2]";
				}
			}
			
			// Do filtering
			if ($_REQUEST['c'] != '') { 
				$where_arr[] = "kh.cawangan_id = '$_REQUEST[c]'" ;
				$paginate_arr[]	= "c=$_REQUEST[c]";
			}
			
			if ($_REQUEST['k'] != '') { 
				$where_arr[] = "pr.keahlian = '$_REQUEST[k]'" ;
				$paginate_arr[]	= "k=$_REQUEST[k]";
			}
			//end filtering
			
			if ($_REQUEST['idx'] != '') { 
				$paginate_arr[]	= "idx=$_REQUEST[idx]";
			}
			if ($_REQUEST['sort_asc'] != '') { 
				$paginate_arr[]	= "sort_asc=$_REQUEST[sort_asc]";
			}

			//month=7&sesi_tahun=2011
			if ($_REQUEST['month'] != '') { 
				$paginate_arr[]	= "month=$_REQUEST[month]";
			}
			if ($_REQUEST['tahun'] != '') { 
				$paginate_arr[]	= "tahun=$_REQUEST[tahun]";
			}
			
			//paging
			if (empty($_REQUEST['p'])) {
				$page = 1;
				$range = " LIMIT $limit";
				$range = "";
			}
			else {
				$page = $_REQUEST['p'];
				$offset = "OFFSET " . ($page-1) * $limit;
				$range = "  LIMIT $limit";
				$range = "";			
			}
			//set maksima utk pembayaran bulk
			$range = " LIMIT $limit";

			//finish where_clause
			$where_clause = join(" AND ", $where_arr);
			if ($where_clause != '') $where_clause = "WHERE " . $where_clause;

			$sql_total_cnt = "SELECT count(ki.*) FROM kunci_induk ki 
			        LEFT JOIN perkhidmatan kh ON kh.no_ahli=ki.no_ahli
					LEFT  JOIN pribadi pr ON pr.no_ahli=ki.no_ahli
					$where_clause";
					//echo $sql_total_cnt;exit;
			if ($res = $DB->Execute($sql_total_cnt)) {
				$dat = $res->FetchNextObject();
				$res_count = $dat->COUNT;
				$last_page = floor($total_count/$limit) + 1;
			}else {
				$_SESSION['error'] = "Error SQL";
				$res_count = 0;
			}
					
			$sql = "SELECT ki.*,kh.cawangan,pr.keahlian
			        ,get_yuran(ki.id,'$selected_year',$selected_month) as yuran_amount
			        ,get_sumbangan(ki.id,'$selected_year',$selected_month) as sumbangan_amount
			        ,get_tabung(ki.id,'$selected_year',$selected_month) as tabung_amount
			        ,get_insuran(ki.id,'$selected_year',$selected_month) as insuran_amount
			        FROM kunci_induk ki 
			        LEFT JOIN perkhidmatan kh ON kh.no_ahli=ki.no_ahli
					LEFT JOIN pribadi pr ON pr.no_ahli=ki.no_ahli
					$where_clause
					ORDER BY ".$sorting_idx[$_SESSION['sortby']]." ".$_SESSION['sort_asc']."
					$range
					$offset
					";
					//echo $sql;//exit;
			if ($res = $DB->Execute($sql)) {
				$res_count = $res->RecordCount();
				$paginate_str = join("&",$paginate_arr);
			}else {
				$_SESSION['error'] = "Error SQL";
			}	
		endif;
		echo $_REQUEST['params'];
		
	break;

	case "payment_bulk_do";
		foreach ($_REQUEST['yuran'] as $k => $v) {
			$y = $v;
			$s = $_REQUEST['sumbangan']["$k"];
			$t = $_REQUEST['tabung']["$k"];
			$i = $_REQUEST['insuran']["$k"];
			//echo $k . " = $y , $s, $t, $i<br>\n";exit;
			$payment = new Payment();
			if ($payment->Load("ahli_id=$k AND sesi_tahun='$_REQUEST[sesi_tahun]' AND month='$_REQUEST[month]'")) { //check if exist already
				if (($y!=$payment->yuran_amount) || ($s!=$payment->sumbangan_amount) || ($t!=$payment->tabung_amount) || ($i!=$payment->insuran_amount)){ //if any changes
					$payment->yuran_amount = ($y=='')?NULL:$y;
					$payment->sumbangan_amount = ($s=='')?NULL:$s;
					$payment->tabung_amount = ($t=='')?NULL:$t;
					$payment->insuran_amount = ($i=='')?NULL:$i;
					//print_r($payment);exit;
					$payment->bayaran_wajib = $WAJIB_BAYAR;
					$payment->save();
				}
				//echo "already exist";exit;
			}
			else { //not exist yet
				$payment->ahli_id = $k;
				$payment->month   = $_REQUEST['month'];
				$payment->sesi_tahun = $_REQUEST['sesi_tahun'];
				$payment->yuran_amount = ($y=='')?NULL:$y;
				$payment->sumbangan_amount = ($s=='')?NULL:$s;
				$payment->tabung_amount = ($t=='')?NULL:$t;
				$payment->insuran_amount = ($i=='')?NULL:$i;
				$payment->date_paid = Date("Y-m-d");
				//print_r($payment);exit;
				$payment->bayaran_wajib = $WAJIB_BAYAR;
				$payment->save();
				//echo "not exist";exit;
			}
			
		}
		$_SESSION['flash'] = 'Penyata telah berjaya dikemaskini.';
		header("Location: ?act=payment_bulk_search&$_REQUEST[params]");

	
	break;

	case "delete_ahli";
		if ($_SESSION['ACCESS_LEVEL']!='1') {
			exitError("Access Denied");
		}
		$ahli = new Ahli();
		$ahli->Load("id='$_REQUEST[id]'");
		
		//if user have payment details, cannot delete
		$sql = "SELECT * FROM payment WHERE ahli_id='$ahli->id'";
		$res = $DB->Execute($sql);
		if ($res->RecordCount() > 0) {
			exitError("Rekod ahli tidak boleh dihapus kerana terdapat data Penyata 5E");
		}
		$sqls[] = "DELETE FROM penama WHERE no_ahli='$ahli->no_ahli'";
		$sqls[] = "DELETE FROM perkhidmatan WHERE no_ahli='$ahli->no_ahli'";
		$sqls[] = "DELETE FROM pribadi WHERE no_ahli='$ahli->no_ahli'";
		$sqls[] = "DELETE FROM kunci_induk WHERE no_ahli='$ahli->no_ahli'";
		
		foreach ($sqls as $sql) {
			$DB->Execute($sql);
			//echo $sql."<br>\n";
		}
		$_SESSION['flash'] = 'Rekod ahli telah berjaya dihapus.';
		header("Location: ?act=search_ahli&$_REQUEST[params]");
	
	break;

	case "clear_test_data";
		$ahli = new Ahli();
		$ahli->Load("id='$_REQUEST[id]'");
				
		$sqls[] = "DELETE FROM penama WHERE no_ahli='$ahli->no_ahli'";
		$sqls[] = "DELETE FROM perkhidmatan WHERE no_ahli='$ahli->no_ahli'";
		$sqls[] = "DELETE FROM pribadi WHERE no_ahli='$ahli->no_ahli'";
		$sqls[] = "DELETE FROM kunci_induk WHERE no_ahli='$ahli->no_ahli'";
		
		foreach ($sqls as $sql) {
			$DB->Execute($sql);
			echo $sql."<br>\n";
		}
		echo "DONE";exit;
	
	break;

	case "edit_projek_dlp";
	
	break;
	
	case "popup_view_staf" :
		$institusi = new Institusi();
		$institusi->Load("kod_institusi='$_SESSION[KOD_INSTITUSI]'");

		$guru_egtukar = new GuruEGTukar();
		$guru_egtukar->Load("no_kp_passport='$_REQUEST[no_kp]'");

		$master_profile = new MasterProfile();
		$master_profile->Load("no_kp='$_REQUEST[no_kp]'");
		

		$master_staf = new MasterStaf();
		$master_staf->Load("master_profile_id='$master_profile->id'");

		include _VIEWPATH_."layout_popup.php";exit;

		break;
	
	case "create_guru_kpm" :
		
		$institusi = new Institusi();
		$institusi->Load("kod_institusi='$_SESSION[KOD_INSTITUSI]'");
		
		//print_r($_REQUEST);exit;

		//create profile guru
		$master_profile = new MasterProfile();
		$no_kp = $_REQUEST['master_profile']['no_kp'];
		$master_profile->Load("no_kp='$no_kp'");
		foreach ($_REQUEST[master_profile] as $k => $v) {
			$master_profile->$k = $v;
			//if (empty($v)) $master_profile->$k = NULL;
		}
		if (empty($master_profile->no_surat_beranak)) $master_profile->no_surat_beranak = NULL;
		if (empty($master_profile->no_passport)) $master_profile->no_passport = NULL;

		if (!$master_profile->save()) { 
			$_SESSION['error'] = "Profile : " . $master_profile->ErrorMsg();
			$act = "new_guru_egtukar";
			break;
		}

		//create staf
		$master_staf = new MasterStaf();
		foreach ($_REQUEST[master_staf] as $k => $v) {
			$master_staf->$k = $v;
			if (empty($v)) $master_staf->$k = NULL;
		}
		if ($master_staf->current_kelas_id == '') $master_staf->current_kelas_id = NULL;
		$master_staf->master_profile_id = $master_profile->id;
		$master_staf->tarikh_mula_berkhidmat = ymd_date($master_staf->tarikh_mula_berkhidmat);
		$master_staf->tarikh_mula_pra = ymd_date($master_staf->tarikh_mula_pra);
		//print_r($master_staf);exit;
		
		if (!$master_staf->save()) { 
			$_SESSION['error'] .= "<br>Murid: ".$master_staf->ErrorMsg();
			$act = "new_guru_egtukar";
			$master_profile->delete();
			break;
		}
		
	
		//$obj->Load('id='.$_REQUEST['id']);
		$_SESSION['flash'] = 'Staf telah berjaya ditambah.';
		header("Location: ?act=view_staf&no_kp=$master_profile->no_kp");
		break;

	case "popup_no_ahli_blm_guna" :
		$s1 = "SELECT max(no_ahli) FROM kunci_induk";
		$r = pg_query($s1);
		list($no_ahli_max) = pg_fetch_row($r);
		$s2 = "SELECT min(no_ahli) FROM kunci_induk";
		$r2 = pg_query($s2);
		list($no_ahli_min) = pg_fetch_row($r2);
		
		$arr_no_ahli = array();
		for($no_ahli_min; $no_ahli_min <= $no_ahli_max; $no_ahli_min++){
			$arr_no_ahli[] = $no_ahli_min;
		
		}
		//print_r($arr_no_ahli);
		// list all no ahli yg ada 
		$sa = "SELECT no_ahli FROM kunci_induk";
		$ra = pg_query($sa);
		$no_ahli = array();
		while($d = pg_fetch_row($ra)){
			$no_ahli[] = $d[0];
		}
		
		$no_ahli_diff = array_diff($arr_no_ahli, $no_ahli);
		
		
		
		include _VIEWPATH_."layout_popup.php";exit;

		break;


	default :
		$sql = "SELECT count(ki.*) FROM kunci_induk ki LEFT JOIN perkhidmatan kh ON kh.no_ahli=ki.no_ahli LEFT JOIN pribadi pr ON pr.no_ahli=ki.no_ahli WHERE pr.keahlian = 'Aktif'";
		$res = $DB->Execute($sql);
		$dat = $res->FetchNextObject();
		$dat = decapitalize($dat);
		$jumlah_ahli_aktif = $dat->count;

		$sql = "SELECT count(ki.*) FROM kunci_induk ki LEFT JOIN perkhidmatan kh ON kh.no_ahli=ki.no_ahli LEFT JOIN pribadi pr ON pr.no_ahli=ki.no_ahli WHERE pr.keahlian = 'Berhenti'";
		$res = $DB->Execute($sql);
		$dat = $res->FetchNextObject();
		$dat = decapitalize($dat);
		$jumlah_ahli_berhenti = $dat->count;

		$sql = "SELECT count(ki.*) FROM kunci_induk ki LEFT JOIN perkhidmatan kh ON kh.no_ahli=ki.no_ahli LEFT JOIN pribadi pr ON pr.no_ahli=ki.no_ahli WHERE pr.keahlian IS NULL";
		$res = $DB->Execute($sql);
		$dat = $res->FetchNextObject();
		$dat = decapitalize($dat);
		$jumlah_ahli_null = $dat->count;

		
		// statistik ahli 
		$s = "select count(*) as bil,  p.bangsa, p.jantina, j.jawatan, kh.jawatan_id from pribadi p 
				LEFT JOIN perkhidmatan kh ON kh.no_ahli=p.no_ahli 
				LEFT JOIN jawatan j ON kh.jawatan_id= j.kod_jawatan 
				WHERE p.keahlian LIKE '%Aktif%'
				GROUP BY p.jantina, j.jawatan, p.bangsa, kh.jawatan_id
				
				";
		$r = $DB->Execute($s);
		//echo $s;
		$data = array();
		$data_kaum_jantina = array();
		$jumlah_perempuan = 0;
		$jumlah_lelaki = 0;
		$jumlah_semua = 0;
		$bangsa_rpt = array('Melayu','Cina','India') ;
		while($d = $r->FetchNextObject(false)){
			$bangsa = $d->bangsa;
			$bil = $d->bil;
			$jawatan = $d->jawatan;
			
			if(is_null($jawatan)) $jawatan = '-Tiada Maklumat-';
			if(!in_array($bangsa,$bangsa_rpt)) $bangsa = 'Lain-lain'; 
			
			$data[$jawatan][$bangsa][$d->jantina] += $bil;
			$data_kaum_jantina[$bangsa][$d->jantina] += $bil;
			
			if($d->jantina == 'Perempuan') $jumlah_perempuan +=$bil;
			if($d->jantina == 'Lelaki') $jumlah_lelaki +=$bil;
		}
		
		array_push($bangsa_rpt, 'Lain-lain');
		$act = "home";
		//header("Location: ?act=search_ahli");
		

}

//this is test
include _VIEWPATH_."$layout.php";

?>
